<?php
namespace Lib;

class Model {
    protected $table     = null;
    protected $pk        = 'id';
    protected $lastSql   = '';    //最后一次执行的sql
    protected $dbConnect = null;

    public function getDbConnect() {
        return $this->dbConnect = get_db_connect();
    }

    /**
     * findById
     * 从数据库中查询self::$pk对应的字段符合条件的一条数据
     *
     * @param mixed $id
     * @param mixed $field
     * @return void
     */
    public function findById($id, $field = null) {
        $id = (int)$id;
        $pk = $this->pk;

        return ($r = $this->findAll(['where' => "{$pk}={$id}", 'field' => $field, 'limit' => 1]))
               ? array_shift($r)
               : false;
    }

    /**
     * find
     * 查询符合条件的一条数据
     *
     * @param mixed $opts
     * @return void
     */
    public function find($opts = []) {
        $opts['limit'] = 1;

        return ($r = self::findAll($opts))
               ? array_shift($r)
               : false;
    }

    /**
     * findAll
     * 查询表中符合$expr条件的数据，如果未指定条件表达式则返回所有数据
     *
     * @param mixed $opt = array(
     *  'where' => '条件表达式',
     *  'field' => '字段列表',      //默认为 *
     *  'offset' => '跳过记录数'
     *  'limit' => '需获取的记录数量',
     *  'table' => '表格名称',
     *  'group' => '分组表达式',
     *  'having' => '分组条件表达式',
     *  'order' => '排序方式',  //默认为$pk降序
     * )
     * @return void
     */
    public function findAll($opt = array()) {
        $sqlTpl = 'SELECT {FIELD} FROM {TABLE} {WHERE} {GROUP} {HAVING} {ORDER} {OFFSET} {LIMIT} ';

        $opt['offset'] = !isset($opt['offset']) ? null : (int)$opt['offset'];
        $opt['limit']  = empty($opt['limit'])   ? null : (int)$opt['limit'];

        $field  = empty($opt['field'])    ? '*'    : $opt['field'];
        $where  = empty($opt['where'])    ? ''     : "WHERE " . $this->buildWhere($opt['where']);
        $offset = is_null($opt['offset']) ? ''     : "OFFSET {$opt['offset']}";
        $limit  = empty($opt['limit'])    ? ''     : "LIMIT {$opt['limit']}";
        $group  = empty($opt['group'])    ? ''     : "GROUP BY {$opt['group']}";
        $having = empty($opt['having'])   ? ''     : "HAVING {$opt['having']}";
        $order  = empty($opt['order'])    ? ''     : "ORDER BY {$opt['order']}";
        $table  = $opt['table'] ?? $this->table;

        if (empty($order) && !empty($this->pk)) {
            $order = 'ORDER BY ' . $this->pk . ' DESC';
        }

        $db  = $this->getDbConnect();
        $sql = str_replace(
            array('{FIELD}', '{TABLE}', '{WHERE}', '{OFFSET}', '{LIMIT}', '{GROUP}', '{HAVING}', '{ORDER}'),
            array($field,    $table,    $where,    $offset,    $limit,    $group,    $having,    $order),
            $sqlTpl
        );

        $this->lastSql = $sql;

        if (!$query = $db->query($sql)) {
            return false;
        }

        return $query->fetchAll(\PDO::FETCH_ASSOC);
    }

    /**
     * insert
     * 插入数据
     *
     * $opts = [
     *   'table' => '', //默认为当前类指定的表名
     *   'dao' => [        //待插入数据
     *      'field' => 'value',     //字段名 => 值
     *   ],
     * ];
     *
     * @param mixed $opts
     * @return void
     */
    public function insert($opts) {
        $table = $opts['table'] ?? $this->getTableName();
        $sql   = 'INSERT INTO {TABLE}({FIELD}) VALUES ({VALUE})';

        if (empty($opts['dao'])) {
            return false;
        }

        $fields  = array_keys($opts['dao']);
        $vals    = array_values($opts['dao']);
        $connect = $this->getDbConnect();
        $vals    = $this->quote($vals);

        $sql = str_replace(
            ['{TABLE}', '{FIELD}', '{VALUE}'],
            [$table, implode(',', $fields), implode(',', $vals)],
            $sql
        );
        $this->lastSql = $sql;

        $r = $connect->exec($sql);

        if (!$r) {
            $msg = $connect->errorInfo();

            if (!empty($msg[2])) {
                throw new \Exception($msg[2]);
            }
        }

        return $connect->lastInsertId();
    }

    public function quote($val) {
        $connect = $this->getDbConnect();

        if (is_array($val)) {
            array_walk($val, function(&$v, $k, $connect) {
                $v = $connect->quote($v);
            }, $connect);
        } else {
            $val = $connect->quote($val);
        }

        return $val;
    }

    /**
     * update
     * 更新记录
     *
     * $opts = [
     *  'table' => '',
     *  'where' => '',
     *  'dao' => [
     *      'field' => 'val',
     *      'field' => ['+', 'val'],    //等同于 field = field + val
     *  ],
     * ];
     *
     * @param mixed $opts
     * @return void
     */
    public function update($opts) {
        $dao = $opts['dao'] ?? null;

        if (empty($dao)) {
            return false;
        }

        $sql   = 'UPDATE {TABLE} SET {VALUE} {WHERE}';
        $table = $opts['table'] ?? $this->getTableName();
        $where = empty($opts['where']) ? '' : $this->buildWhere($opts['where']);
        $where = empty($where)         ? '' : " WHERE {$where}";
        $connect = $this->getDbConnect();
        $values  = [];

        foreach ($opts['dao'] as $k => $v) {
            if (is_array($v)) {
                if (count($v) < 2) {
                    continue;
                }

                $v = $k . $v[0] . $this->quote($v[1]);
            } else {
                $v = self::quote($v);
            }

            $values[] = $k . '=' . $v;
        }

        $value = implode(' , ', $values);
        $sql   = str_replace(
            ['{TABLE}', '{VALUE}', '{WHERE}'],
            [$table, $value, $where],
            $sql
        );
        $this->lastSql = $sql;
        $r = $connect->exec($sql);

        if ($r === false) {
            $msg = $connect->errorInfo();

            if (!empty($msg[2])) {
                throw new \Exception($msg[2]);  
            }
        }

        return $r;
    }

    public function save($d) {
        $act  = empty($d[$this->pk]) ? 'insert' : 'update';
        $opts = [];

        if ($act == 'update') {
            $opts['where'] = [$this->pk => $d[$this->pk]];
            unset($d[$this->pk]);
        }

        $opts['dao'] = $d;

        return $this->$act($opts);
    }

    public function getLastSql() {
        return $this->lastSql;
    }

    /**
     * getTableName
     * 返回表格名称
     *
     * @return void
     */
    public function getTableName() {
        return $this->table;
    }

    /**
     * buildWhere
     * 组装where条件表达式
     *
     * $whereOpts示例
     * $whereOpts = [
     *  'id' => 123,
     *  'name' => ['LIKE', '%name%'],
     *  '_expr' => 'status <> 1 OR sex=1',  //buildWhere会在_expr的条件块中加入()包围条件
     * ];
     *
     * @param mixed $whereOpts 
     * @return void
     */
    public function buildWhere($whereOpts) {
        if (!is_array($whereOpts)) {
            return $whereOpts;
        }

        $r = [];

        foreach ($whereOpts as $k => $v) {
            if (is_array($v)) {
                $op  = $v[0];
                $val = $this->quote($v[1]);
                $val = is_array($val) ? '(' . implode(',', $val) . ')' : $val;
            } elseif ($k == '_expr') {
                $val = '(' . $v . ')';
                $k   = $v = '';
            } else {
                $op  = '=';
                $val = $this->quote($v);
            }

            $r[] = $k . $op . $val; 
        }

        return implode(' AND ', $r);
    }

    /**
     * buildJsonOpts
     * 组装数据表中opts字段值，opts字段一般为jsonb类型
     * 如果$d中有$this->pk，则获取$this->pk对应的记录，然后将$d的值合并
     * 至记录中opts字段对应的值
     *
     * @param mixed $d
     * @param mixed $field  jsonb类弄对应的字段名称，一般为opts
     * @return void
     */
    public function buildJsonOpts($d, $field = 'opts') {
        $id    = (int)($d[$this->pk] ?? 0);
        $field = $field ?? 'opts';
        $dao   = [];

        if (!empty($id) && $record = $this->find(['where' => [$this->pk => $id]])) {
            $dao = json_decode($record[$field], true) ?: [];
        }

        $dao = array_merge($dao, $d);

        return json_encode($dao);
    }

}
